---明细DDL
---
CREATE EXTERNAL TABLE jms_dm.dm_upload_in_time_detail_dt(
  billcode string COMMENT '运单号', 
  network_code string COMMENT '责任网点编码', 
  network_name string COMMENT '责任网点名称', 
  scan_type string COMMENT '扫描类型', 
  scan_time timestamp COMMENT '扫描时间', 
  upload_time timestamp COMMENT '上传时间', 
  consume_time int COMMENT '上传时长（分钟）', 
  item_type string COMMENT '明细类型other(其他),sign(正常签收),collect(代签收),transfer(中转),taking(揽收),deliver(派件)', 
  status int COMMENT '状态1有效0无效', 
  create_time timestamp COMMENT '创建时间', 
  update_time timestamp COMMENT '更新时间', 
  recordid string COMMENT '扫描主键', 
  scanusercode string COMMENT '扫描雇员编号', 
  scanuser string COMMENT '扫描雇员', 
  packagecode string COMMENT '所属袋号(包号)', 
  bscan_time timestamp COMMENT '包号的扫描时间', 
  bupload_time timestamp COMMENT '包号的上传时间', 
  pistolid string COMMENT '巴枪id', 
  source_code string COMMENT '订单来源编号(新)', 
  source_name string COMMENT '订单来源编号(新)', 
  network_type int COMMENT '网点类型', 
  agent_code string COMMENT '代理编码', 
  agent_name string COMMENT '代理区名称', 
  manage_code string COMMENT '管理大区code', 
  manage_name string COMMENT '管理大区name', 
  fran_code string COMMENT '加盟商code', 
  fran_name string COMMENT '加盟商name', 
  date_time date COMMENT '查询日期'
  )
COMMENT '上传及时率明细'
PARTITIONED BY ( 
  dt string COMMENT '上传时间', 
  intime string COMMENT '1不及时0及时') 
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_upload_in_time_detail_dt'
TBLPROPERTIES (
    'discover.partitions' = 'false',
    'parquet.column.index.access' = 'true'
);
-----汇总DDL
-----
CREATE EXTERNAL TABLE jms_dm.dm_upload_in_time_count_dt(
  scan_date date COMMENT '上传日期',
  manage_code string COMMENT '管理大区code', 
  manage_name string COMMENT '管理大区name', 
  agent_code string COMMENT '代理区code', 
  agent_name string COMMENT '代理区名称', 
  fran_code string COMMENT '加盟商code', 
  fran_name string COMMENT '加盟商名称', 
  network_code string COMMENT '责任网点code', 
  network_name string COMMENT '责任网点name', 
  taking_num int COMMENT '揽收及时量', 
  taking_no_num int COMMENT '揽收不及时量', 
  transfer_num int COMMENT '转运及时量', 
  transfer_no_num int COMMENT '转运不及时量', 
  sign_num int COMMENT '签收及时量', 
  sign_no_num int COMMENT '签收不及时量', 
  deliver_num int COMMENT '派件及时量', 
  deliver_no_num int COMMENT '派件不及时量', 
  collect_num_proxy int COMMENT '代收上传及时量(代理点)', 
  collect_no_num_proxy int COMMENT '代收不及时量(代理点)', 
  collect_num int COMMENT '代收上传及时量(终端)', 
  collect_no_num int COMMENT '代收不及时量(终端)', 
  other_num int COMMENT '其他及时量', 
  other_no_num int COMMENT '其他不及时量', 
  all_cnt int COMMENT '整体上传及时量', 
  all_no_cnt int COMMENT '整体不上传及时量', 
  date_time date COMMENT '查询日期', 
  fine_sum decimal(16,2) COMMENT '不及时量预罚款金额(单位元)', 
  source_name string COMMENT '订单来源'
  )
COMMENT '上传及时率明细'
PARTITIONED BY (
  dt string COMMENT '上传时间')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_upload_in_time_count_dt'
TBLPROPERTIES (
    'discover.partitions' = 'false',
    'parquet.column.index.access' = 'true'
);

alter table bidefault.dm_upload_in_time_count_dt add columns
(
      network_type  int comment'网点类型 4中心 5 集散 6网点'
     , scanusercode string comment'扫描员code'
     , scanuser string comment'扫描员'
     , cnt_type int comment'统计类型 1网点+订单来源 2网点+派件员 '
)cascade